
package com.example.gorannet;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.widget.Toast;

public class OpenHelper extends SQLiteOpenHelper
{
    private static final String strDatabaseName          = "mydatabase.db";

    private static final int    intDatabaseVersion       = 3;

    private static final String strSystemSettingsTable = "System_Settings_TBL";

    private static final String strGCMSettingsTable    = "GCM_Settings_TBL";

    private static final String strRegistrationTable   = "Registration_TBL";

    private static final String strMessagesTable       = "Messages_TBL";

    private static final String strTicketsTable        = "Tickets_TBL";

    private static final String strTicketDetailsTable  = "Ticket_details_TBL";

    private static final String strCommentsTable       = "Comments_TBL";

    private static final String strCSUsersTable        = "CS_users_TBL";

    private static final String strLanguageTable        = "i18n_TBL";

    private static final String strTicketsSurveyTable        = "CS_tickets_survey_TBL";

    private Context             context                = null;

    OpenHelper(Context context)
    {
        super(context, strDatabaseName, null, intDatabaseVersion);
        this.context = context;
        onCreate(this.getWritableDatabase());
    }

    @Override
    public void onCreate(SQLiteDatabase sqliteDatabase)
    {
        createTables(sqliteDatabase);
    }

    private void createTables(SQLiteDatabase sqliteDatabase)
    {
        sqliteDatabase.execSQL("CREATE TABLE IF NOT EXISTS " + strRegistrationTable
                + " (recordID INTEGER PRIMARY KEY, username TEXT, password TEXT, login_count TEXT)");
        createAdminAccount(sqliteDatabase);
        sqliteDatabase.execSQL("CREATE TABLE IF NOT EXISTS " + strSystemSettingsTable
                + " (recordID INTEGER PRIMARY KEY, Application_Name TEXT, Key TEXT, Value TEXT)");
        sqliteDatabase.execSQL("CREATE TABLE IF NOT EXISTS " + strGCMSettingsTable
                + "  (recordID INTEGER PRIMARY KEY, Sender_ID TEXT, Server_URL TEXT)");



        sqliteDatabase.execSQL("CREATE TABLE IF NOT EXISTS " + strTicketDetailsTable
                + " (ticket_id TEXT PRIMARY KEY, subject TEXT, telephone TEXT, status TEXT," +
                " priority TEXT, createdOn TEXT, lastUpdate TEXT, neighbourhood TEXT, alley TEXT, Street Text," +
                " house TEXT, address2 TEXT, site TEXT, pairNo TEXT, username TEXT, password TEXT," +
                " user_id TEXT, assignedTo TEXT, conversation TEXT, posted TEXT, tctDet TEXT, sender TEXT, receiver TEXT, arrTime long)");

        sqliteDatabase.execSQL("CREATE TABLE IF NOT EXISTS " + strCommentsTable
                + " (record_id INTEGER PRIMARY KEY,ticket_id TEXT, comments TEXT, fromTo TEXT, isMine TEXT, time Long)");

        sqliteDatabase.execSQL("CREATE TABLE IF NOT EXISTS " + strCSUsersTable
                + " (user_id TEXT PRIMARY KEY,user_name TEXT, password TEXT, office_id TEXT, group_id TEXT, active TEXT, full_name TEXT, f_login_status TEXT)");

        sqliteDatabase.execSQL("CREATE TABLE IF NOT EXISTS " + strTicketsSurveyTable
                + " (record_id int PRIMARY KEY, ticket_no TEXT, employee_id TEXT," +
                " communication TEXT, understanding TEXT, professionalism TEXT, " +
                "time_to_complete TEXT, ticket_date LONG, customer_signature BLOB, employee_signature BLOB NULL, flag TEXT)");

    }
 
    private void createAdminAccount(SQLiteDatabase sqliteDatabase)
    {
        //TODO: later on check that you only insert one admin account
        String SQL = "select username from " + strRegistrationTable + " where username = 'admin' and password ='admin'";
        Cursor cursor = sqliteDatabase.rawQuery(SQL, null);
        if (!cursor.moveToFirst())
        {
            sqliteDatabase.execSQL("insert into " + strRegistrationTable + " (username, password) values ('admin','admin') ");

        }

    }

    @Override
    public void onUpgrade(SQLiteDatabase sqliteDatabase, int intOldVersion, int intNewVersion)
    {
        Toast.makeText(context,
                "Database File is being upgraded to " + sqliteDatabase.getVersion(),
                Toast.LENGTH_LONG).show();
        sqliteDatabase.execSQL("DROP TABLE IF EXISTS " + strRegistrationTable);
        sqliteDatabase.execSQL("DROP TABLE IF EXISTS " + strSystemSettingsTable);
        sqliteDatabase.execSQL("DROP TABLE IF EXISTS " + strGCMSettingsTable);
        sqliteDatabase.execSQL("DROP TABLE IF EXISTS " + strTicketDetailsTable);
        sqliteDatabase.execSQL("DROP TABLE IF EXISTS " + strCommentsTable);

        createTables(sqliteDatabase);
    }

    public void deleteTable(SQLiteDatabase sqliteDatabase, String strTableName)
    {
        sqliteDatabase.execSQL("DROP TABLE IF EXISTS " + strTableName);

    }

}
